﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class LoginRepository : IRepository<Login>
    {
        private IDbCommand Command { get; set; }

        public LoginRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public Login GetById(int id)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idLogin, Usuario, Senha FROM tbLogin WHERE idLogin=" + id;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    Login login = new Login()
                    {
                        ID = int.Parse(reader["idLogin"].ToString()),
                        Usuario = reader["Usuario"].ToString(),
                        Senha = reader["Senha"].ToString()
                    };
                    return login;
                }
                else
                {
                    return null;
                    // throw new Exception("Login não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public Login GetByUsernamePassword(string username, string password)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idLogin, Usuario, Senha FROM tbLogin WHERE Usuario = '" + username + 
                                                                                     "' AND Senha = '" + password + "'";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    Login login = new Login()
                    {
                        ID = int.Parse(reader["idLogin"].ToString()),
                        Usuario = reader["Usuario"].ToString(),
                        Senha = reader["Senha"].ToString()
                    };
                    return login;
                }
                else
                {
                    throw new Exception("Login não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<Login> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idLogin, Usuario, Senha FROM tbLogin";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<Login> list = new List<Login>();
                while (reader.Read())
                {
                    Login login = new Login()
                    {
                        ID = int.Parse(reader["idLogin"].ToString()),
                        Usuario = reader["Usuario"].ToString(),
                        Senha = reader["Senha"].ToString()
                    };
                    list.Add(login);
                }
                return list.AsQueryable<Login>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(Login entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "INSERT INTO tbLogin (idLogin, Usuario, Senha) VALUES (" + entity.ID + ",'" + entity.Usuario + "', '" + entity.Senha + "')";
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();

                return entity.ID;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(Login entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbLogin WHERE idLogin=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(Login entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "UPDATE tbLogin SET Usuario='" + entity.Usuario + "', Senha='" + entity.Senha + "' WHERE idLogin=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
